package com.main.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.main.pojo.Uploader;
import com.main.utils.CommonUtils;
import com.main.utils.Constant;
import com.main.utils.HibernateUtils;

@Transactional
@Repository
public class UploaderDAOImpl implements UploaderDAO {

	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public Uploader getUploaderByEmail(String email) throws Exception {
		Uploader uploader = null;
		try {
			String hql = "SELECT u.id, u.name, u.avatar FROM Uploader u WHERE u.email = :email";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString("email", email);
			Object[] obj = (Object[]) query.uniqueResult();
			if (obj != null) {
				uploader = new Uploader();
				uploader.setId((Integer) obj[0]);
				uploader.setName((String) obj[1]);
				uploader.setAvatar((String) obj[2]);
			}
			return uploader;
		} catch (Exception e) {
			throw e;
		}
	}
	
	@Override
	public Uploader getUploader(String email, String password) throws Exception {
		Uploader uploader = null;
		try {
			String hql = "SELECT u.id, u.name, u.avatar FROM Uploader u WHERE u.email = :email AND u.password = :password";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString("email", email);
			query.setString("password", CommonUtils.getMD5(password));
			Object[] obj = (Object[]) query.uniqueResult();
			if (obj != null) {
				uploader = new Uploader();
				uploader.setId((Integer) obj[0]);
				uploader.setName((String) obj[1]);
				uploader.setAvatar((String) obj[2]);
			}
			return uploader;
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public Uploader getUploaderById(int id) throws Exception {
		try {
			return (Uploader) sessionFactory.getCurrentSession().get(Uploader.class, id);
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public Uploader insertUploader(Uploader uploader) throws Exception {
		try {
			HibernateUtils.IdentityInsertOn(sessionFactory, "UPLOADER");
			Integer id = (Integer) sessionFactory.getCurrentSession().save(uploader);
			uploader.setId(id);
			return uploader;
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public Uploader insertUploader(String email, String name, String avatar) throws Exception {
		try {
			Uploader uploader = new Uploader();
			uploader.setEmail(email);
			uploader.setName(name);
			uploader.setAvatar(avatar);
			HibernateUtils.IdentityInsertOn(sessionFactory, "UPLOADER");
			Integer id = (Integer) sessionFactory.getCurrentSession().save(uploader);
			uploader.setId(id);
			return uploader;
		} catch (Exception e) {
			throw e;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Uploader> getTopUploader(String rankingType, int topNumber) throws Exception {
		try {
			String sql = "";
			if(Constant.ALL.equals(rankingType)){
				sql = "SELECT TOP " + topNumber + " u.Id, u.Name, u.Avatar, u.LikeNumber FROM UPLOADER u ORDER BY u.LikeNumber DESC";
			} else {
				sql = "SELECT TOP " + topNumber + " u.Id, u.Name, u.Avatar, r.LikeNumber FROM UPLOADER u LEFT JOIN UPLOADER_RANKING r ON u.Id = r.UploaderId "
						+ "WHERE r.RankingType = '" + rankingType + "' ORDER BY r.LikeNumber DESC";
			}
			
			SQLQuery sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(sql)
					.addScalar("Id", Hibernate.INTEGER).addScalar("Name", Hibernate.STRING)
					.addScalar("Avatar", Hibernate.STRING).addScalar("LikeNumber", Hibernate.INTEGER);
			List<Object[]> list = sqlQuery.list();
			List<Uploader> result = new ArrayList<Uploader>();
			for (Object[] obj : list) {
				Uploader uploader = new Uploader();
				uploader.setId((Integer) obj[0]);
				uploader.setName((String) obj[1]);
				uploader.setAvatar((String) obj[2]);
				uploader.setLikeNumber((Integer) obj[3]);
				result.add(uploader);
			}
			return result;
		} catch (Exception e) {
			throw e;
		}
	}

	@Override
	public String updatePassword(int uploaderId, String newPassword) throws Exception {
		try {
			String pass = CommonUtils.getMD5(newPassword);
			String hql = "UPDATE Uploader u SET u.password = :password WHERE u.id = :id";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString("password", pass);
			query.setInteger("id", uploaderId);
			if(query.executeUpdate() > 0){
				return pass;
			}
		} catch (Exception e) {
			throw e;
		}
		return "";
	}

	@Override
	public void updateInfo(int uploaderId, String name, String avatar, String description) throws Exception {
		try {
			String hql = "UPDATE Uploader u SET u.name = :name , u.avatar = :avatar , u.description = :description WHERE u.id = :id";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			query.setString("name", name);
			query.setString("avatar", avatar);
			query.setString("description", description);
			query.setInteger("id", uploaderId);
			query.executeUpdate();
		} catch (Exception e) {
			throw e;
		}		
	}


}
